﻿using AngelCMS.module.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace AngelCMS.module.DAO
{
    public class UserInfoDAL
    {
        AccessHelper ah = new AccessHelper();
        public int AddUsersInfo(UserInfo u) {
            string  sql= @"INSERT INTO  UsersInfo (UserName, PhoneNumber, TicketCode, Grades, IsFinal, MatchID,AreaID) 
                    VALUES
                   ('{0}', '{1}', '{2}', '{3}', {4}, '{5}',{6})";
            string sqlformat = string.Format(sql, u.UserName, u.PhoneNumber, u.TicketCode, u.Grades, u.IsFinal,u.MatchID,u.AreaID);
            return ah.excuteSqlInt(sqlformat);
        }

        public int Delete(int id)
        {

            string sql = "delete  from UsersInfo  where id=" + id;
          return ah.excuteSqlInt(sql);

        }

        public int Edit(UserInfo u, int id)
        {
            string sql = @"Update UsersInfo set UserName='{0}',PhoneNumber='{1}',TicketCode='{2}',
                                                Grades='{3}',IsFinal={4},MatchID={5} ,AreaID={6} where id={7}";
            string sqlformat = string.Format(sql,  u.UserName, u.PhoneNumber, u.TicketCode, u.Grades, u.IsFinal, u.MatchID, u.AreaID,id);
            return ah.excuteSqlInt(sqlformat);
        }

        public DataTable GetUserInfoById(int id)
        {

            string sql = "select*  from  UsersInfo where id=" + id;
            return ah.dataTable(sql);
        }

        public DataTable GetMatch(string matchArea, string phoneNumber, string userName, string matchName)
        {
            string sql = @"SELECT b.*, a.MatchName,a.QrCode, c.AreaName
                     FROM (UsersInfo AS b LEFT JOIN MatchInfo AS a ON a.ID = b.MatchID) 
                      LEFT JOIN MatchArea AS c ON b.AreaID=c.ID
                where a.MatchName='{0}' and b.PhoneNumber='{1}'  and b.userName='{2}' and c.ID={3}";
            sql = string.Format(sql, matchName,phoneNumber,userName,matchArea);
            return ah.dataTable(sql);
        }

        public DataTable GeSelectList(string MatchName) { 
            string sql = "select MatchArea from MatchInfo where MatchName='{0}' group by MatchArea";
            sql= string.Format(sql,MatchName.Trim());
            return ah.dataTable(sql);
        }

        public DataTable GeSelectList()
        {
            string sql = "select ID,MatchName from MatchInfo";
            return ah.dataTable(sql);
        }


        public DataTable GetCurentMacthinfo() {

            string sql = "select  * from MatchInfo where iscurent=1 order by id desc";
            return ah.dataTable(sql);
        }

        public DataTable GetUsersInfo(string keyword, int pagesize, int pageindex, string orderby, out int count)
        {

            string fldName ="*";
            string strWhere = "";
            if (!string.IsNullOrEmpty(keyword))
            {
                 strWhere = string.Format("UserName like '%{0}%' or PhoneNumber like '%{0}%'  ", keyword);
            }
            return ah.FengYe("UserMatch", fldName, "id", "", strWhere, pagesize, pageindex, out count);
        }


    }
}